-- =============================================
-- This stored procedure retrieves records from vBoTagHierarchy records
-- that are child records of @ParentHierarchyKey and @UserKey has access to assign.
-- =============================================
CREATE PROCEDURE [dbo].[asi_GetAssignableChildTagKey]
    @ParentHierarchyKey uniqueidentifier,
    @UserKey uniqueidentifier
AS
BEGIN
    SELECT HierarchyKey FROM vBoTagHierarchy
     WHERE ParentHierarchyKey = @ParentHierarchyKey
       AND EXISTS (SELECT 1
      FROM AccessItem INNER JOIN UserToken
         ON AccessItem.Grantee = UserToken.Grantee
    WHERE AccessItem.AccessKey = vBoTagHierarchy.AssignAccessKey
      AND UserToken.UserKey = @UserKey
-- At least one of Control/Read/Add/Edit/Delete
      AND (AccessItem.Permission&31)>0)
END

GO